using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Avalonia;
using Avalonia.Controls;
using Avalonia.Controls.ApplicationLifetimes;
using Avalonia.Controls.Shapes;
using Avalonia.Platform.Storage;
using CommunityToolkit.Mvvm.ComponentModel;
using CommunityToolkit.Mvvm.Input;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using MsBox.Avalonia.Enums;
using Nlnet.Avalonia.Controls;
using OfficeOpenXml;
using 畜牧业审核程序国产.Models;
using 畜牧业审核程序国产.Models.DataGridMsgs;
using 畜牧业审核程序国产.Models.PreReview;
using 畜牧业审核程序国产.Models.ToolsClass;
using Path = System.IO.Path;

namespace 畜牧业审核程序国产.ViewModels.PreReviewViewModels;

public class PreReviewViewModel : ObservableObject
{
    public RelayCommand? ChoseExcelCommand { get; private set; }
    public RelayCommand? CreateLedgerCommand { get; private set; }
    public RelayCommand? ReviewLedgerCommand { get; private set; }
    public RelayCommand? ShowTheDataInLedgerCommand { get; private set; }
    public RelayCommand? OpenLedgerCommand { get; private set; }
    public RelayCommand? OpenFloatingSubmitWindowCommand { get; private set; }
    public RelayCommand? OpenLedgerFolderCommand { get; private set; }
    public RelayCommand? ChoseLedgerFolderCommand { get; private set; }
    
    private ObservableCollection<string> _animalKindList = new ObservableCollection<string>();

    public ObservableCollection<string> AnimalKindList 
    {
        get { return _animalKindList; }
        set { SetProperty(ref _animalKindList, value); }
    }
    private string _selectedAnimalKind;

    public string SelectedAnimalKind
    {
        get { return _selectedAnimalKind; }
        set => SetProperty(ref _selectedAnimalKind, value);
    }

    private ObservableCollection<PreReviewSwineMsgs> _massPigCln = new ObservableCollection<PreReviewSwineMsgs>();

    public ObservableCollection<PreReviewSwineMsgs> MassPigCln
    {
        get { return _massPigCln; }
        set { SetProperty(ref _massPigCln, value); }
    }
    private ObservableCollection<PreReviewCowMsgs> _massCowCln = new ObservableCollection<PreReviewCowMsgs>();

    public ObservableCollection<PreReviewCowMsgs> MassCowCln
    {
        get { return _massCowCln; }
        set { SetProperty(ref _massCowCln, value); }
    }
    private List<ExcelWorksheet> sheetsOfSwine = new List<ExcelWorksheet>();
    private List<ExcelWorksheet> sheetsOfCow = new List<ExcelWorksheet>();
    private List<ExcelWorksheet> sheetsOfGoat = new List<ExcelWorksheet>();
    private List<ExcelWorksheet> sheetsOfPoultry = new List<ExcelWorksheet>();



    private SettingManager _settingManager = new SettingManager();
    public PreReviewViewModel()
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        if (!string.IsNullOrWhiteSpace( _settingManager.LedgerPath))
        {
            SelectedSingleExcelLedgerPath = _settingManager.LedgerPath;
            GetAllAnimalKind().Wait();
        }

        if (!string.IsNullOrWhiteSpace(_settingManager.LedgerFolderPath))
        {
            SelectedLedgerFolderPath = _settingManager.LedgerFolderPath;
            //todo
        }
        
        this.ChoseExcelCommand = new RelayCommand(() => ChoseExcel());
        this.CreateLedgerCommand = new RelayCommand(async () => await CreateLedger());
        this.ReviewLedgerCommand = new RelayCommand(() => ReviewLedger());
        this.ShowTheDataInLedgerCommand = new RelayCommand(async () => await ShowTheDataInLedger());
        this.OpenLedgerCommand = new RelayCommand(async () => await OpenLedger());
        this.OpenFloatingSubmitWindowCommand = new RelayCommand(() => OpenFloatingSubmitWindow());
        this.ChoseLedgerFolderCommand = new RelayCommand(async () => await ChoseLedgerFolderPath());
        this.OpenLedgerFolderCommand = new RelayCommand(async () => await OpenLedgerFolder());
    }
    //private IStorageFile? _selectedExcelStorageFile;


    //public IStorageFile? SelectedExcelStorageFile
    //{
    //    get { return _selectedExcelStorageFile; }
    //    set
    //    {
    //        SetProperty(ref _selectedExcelStorageFile, value);
    //        if (value != null)
    //        {
    //            SelectedSingleExcelLedgerPath = value.TryGetLocalPath();
    //        }
    //    }
    //}
    private string _SelectedSingleExcelLedgerPath;

    public string SelectedSingleExcelLedgerPath
    {
        get { return _SelectedSingleExcelLedgerPath; }
        set {
            

            SetProperty(ref _SelectedSingleExcelLedgerPath, value);
            
        }
    }
    private string _selectedLedgerFolderPath;

    public string SelectedLedgerFolderPath
    {
        get { return _selectedLedgerFolderPath; }
        set => SetProperty(ref _selectedLedgerFolderPath, value);
    }

    



    private readonly int _seasonNow = Global.IntSeasonNow;

    private void OpenFloatingSubmitWindow()
    {
        var mainWindow = GetMainWindow();
        var topLevel = TopLevel.GetTopLevel(mainWindow);
        var floatingWindow = new FloatingSubmitWindow();
        floatingWindow.Show();
    }

    private async Task OpenLedgerFolder()
    {
        if (string.IsNullOrWhiteSpace(SelectedLedgerFolderPath) || System.IO.Directory.Exists(SelectedLedgerFolderPath) == false)
        {
            await MyMessagebox.Show("请先选择审核台账所在的文件夹", ButtonEnum.Ok, Icon.Error);
            return;
        }
        if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
        {
            Process.Start("explorer.exe", SelectedLedgerFolderPath);
        }
        else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
        {
            Process.Start("xdg-open", SelectedLedgerFolderPath); // 对于大多数Linux桌面环境
            // 如果你知道使用的具体文件管理器，也可以直接调用，例如：
            // Process.Start("nautilus", folderPath); // GNOME
            // Process.Start("dolphin", folderPath);  // KDE
        }
        else if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
        {
            Process.Start("open", SelectedLedgerFolderPath);
        }
    }

    private async Task ChoseLedgerFolderPath()
    {
        var mainWindow = GetMainWindow();
        var topLevel = TopLevel.GetTopLevel(mainWindow);
        var folder =await topLevel.StorageProvider.OpenFolderPickerAsync(new FolderPickerOpenOptions()
        {
            Title = "请选择存放台账的文件夹",
            AllowMultiple = false,
        });
        //await MyMessagebox.Show(folder.Result[0].TryGetLocalPath());
        
        if (folder !=null && folder.Count>0)
        {
            
            SelectedLedgerFolderPath = folder[0].TryGetLocalPath();
        }
    }

  

    private async Task OpenLedger()
    {
        if (string.IsNullOrWhiteSpace(SelectedSingleExcelLedgerPath) || !System.IO.File.Exists(SelectedSingleExcelLedgerPath))
        {
            await MyMessagebox.ShowAsyncInMainWindow("未选择台账!", Icon.Warning, ButtonEnum.Ok);
            return;
        }
       
        try
        {
            // 使用系统默认应用打开文件
            ProcessStartInfo psi = new ProcessStartInfo()
            {
                FileName = SelectedSingleExcelLedgerPath,
                UseShellExecute = true //使用系统shell来启动进程，从而打开默认应用程序
            };

            Process.Start(psi);
        }
        catch (Exception ex)
        {
            // 处理可能发生的异常，比如找不到合适的默认应用程序等
           await MyMessagebox.ShowAsyncInMainWindow($"无法打开文件：{ex.Message}",Icon.Warning,ButtonEnum.Ok);
        }
    }

    private async Task ChoseExcel()
    {
        var mainWindow = GetMainWindow();
        var topLevel = TopLevel.GetTopLevel(mainWindow);
        var files = await topLevel.StorageProvider.OpenFilePickerAsync(new FilePickerOpenOptions()
        {
            Title = "选择Excel台账",
            AllowMultiple = false,
            FileTypeFilter = new[]
                {
                    new FilePickerFileType("Excel Files")
                    {
                        //Patterns = new[] { "*.xls", "*.xlsx" }
                        Patterns = new[] {  "*.xlsx" }
                    }
                }
        });
        if (files != null && files.Count > 0)
        {
            IStorageFile? SelectedExcelStorageFile = files[0];// 获取用户选择的文件
            SelectedSingleExcelLedgerPath = SelectedExcelStorageFile.TryGetLocalPath();
            await GetAllAnimalKind();
            //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            //打开文件流
        //    await using (var stream = await SelectedExcelStorageFile.OpenReadAsync())//异步地释放实现了 IAsyncDisposable 接口的资源
        //                                                                             //selectedFile.OpenReadAsync() 是一个异步方法，它会返回一个 Task<Stream>，表示一个异步操作，最终会返回一个文件流（Stream）。
        //    /*await using 是 C# 8.0 引入的语法糖，用于异步地释放实现了 IAsyncDisposable 接口的资源。

        //Stream 实现了 IAsyncDisposable 接口，因此可以使用 await using 来确保文件流在使用完毕后能够异步地释放资源。

        //传统的 using 语句只能同步释放资源（调用 Dispose 方法），而 await using 会调用 DisposeAsync 方法，确保资源能够异步释放。*/
        //    {
        //        // 使用 EPPlus 操作 Excel 文件  
        //        //using (var package = new OfficeOpenXml.ExcelPackage(stream))
        //        //{
        //        //    var wb = package.Workbook;// 获取工作簿
        //        //                              //接下去对每一个sht表进行操作
        //        //    foreach (ExcelWorksheet sht in wb.Worksheets)
        //        //    {

        //        //    }

        //        //}
        //    }
        }



    }
    /// <summary>
    /// 审核台账
    /// </summary>
    public async void ReviewLedger()
    {
        if (string.IsNullOrWhiteSpace(SelectedSingleExcelLedgerPath))
        {
            await MyMessagebox.ShowAsyncInMainWindow("请先选择台账文件", Icon.Error, ButtonEnum.Ok);
            return;
        }

        using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(fileName: SelectedSingleExcelLedgerPath)))
        {
            try
            {
                var wb = package.Workbook;// 获取工作簿
                                          //接下去对每一个sht表进行操作
                if (wb.Worksheets.Count == 0)
                {
                    await MyMessagebox.ShowAsyncInMainWindow("选择的台账没有sheet表!", Icon.Error, ButtonEnum.Ok);
                    return;
                }

                List<string> 存在非数值单元格的sht表集合 = new List<string>();
                foreach (var sht in wb.Worksheets)
                {
                   
                    

                    if (sht.Cells[1, 1].Value != null && sht.Cells[1, 1].Value.ToString().Contains("养殖") && sht.Cells[1, 1].Value.ToString().Contains("台账") && sht.Cells[1, 1].Value.ToString().Contains("猪"))
                    {
                        //对生猪台账进行审核
                       
                            Swine swine = new Swine();
                            if (swine.检查全年填报区域是否有非数值单元格(sht))
                            {
                                存在非数值单元格的sht表集合.Add(sht.Name);
                                continue;
                            }
                            //先进行强制性错误审核
                            if (swine.MandatoryReview(sht).Equals("本地数据未导入上一年第四季度数据"))
                            {
                                await MyMessagebox.ShowAsyncInMainWindow($"{sht.Name}本地数据未导入上一年第四季度数据", Icon.Error, ButtonEnum.Ok);
                            }
                            //再进行核实性错误审核
                            swine.VerificationReview(sht);
                       
                    }
                    else if (sht.Cells[1, 1].Value != null && sht.Cells[1, 1].Value.ToString().Contains("养殖") && sht.Cells[1, 1].Value.ToString().Contains("台账") && sht.Cells[1, 1].Value.ToString().Contains("牛"))
                    {
                       //进行牛的审核
                       Cow cow = new Cow();
                       if (cow.检查全年填报区域是否有非数值单元格(sht))
                       {
                           存在非数值单元格的sht表集合.Add(sht.Name);
                           continue;
                        }
                        //先进行强制性错误审核
                        if (cow.MandatoryReview(sht) != null)
                        {
                            await MyMessagebox.ShowAsyncInMainWindow($"{sht.Name}本地数据未导入上一年第四季度数据", Icon.Error, ButtonEnum.Ok); MyMessagebox.ShowAsyncInMainWindow($"{sht.Name}{cow.MandatoryReview(sht)}", Icon.Error, ButtonEnum.Ok);
                        }
                       cow.VerificationReview(sht);

                    }

                }
                package.Save();
                if (存在非数值单元格的sht表集合.Count>0)
                {
                    string shtNames = string.Join("、", 存在非数值单元格的sht表集合);
                    await MyMessagebox.ShowAsyncInMainWindow($"{shtNames}共{存在非数值单元格的sht表集合.Count}个养殖户台账未审核,因存在非数值单元格,已经标成紫色,请检查", Icon.Error, ButtonEnum.Ok);
                    
                }
                else
                {
                    await MyMessagebox.ShowAsyncInMainWindow("台账审核完成", Icon.Success, ButtonEnum.Ok);
                }

            }
            catch (InvalidOperationException ex)
            {
                await MyMessagebox.ShowAsyncInMainWindow("台账正在被占用,请先关闭", Icon.Error, ButtonEnum.Ok);
            }
        }
    }

    public async Task CreateLedger()//使用台账生成模板生成台账
    {

        //先选择好台账模板
        var mainWindow = GetMainWindow();
        var topLevel = TopLevel.GetTopLevel(mainWindow);
        var files = await topLevel.StorageProvider.OpenFilePickerAsync(new FilePickerOpenOptions()
        {
            Title = "选择模板",
            AllowMultiple = false,
            FileTypeFilter = new[]
                {
                    new FilePickerFileType("Excel Files")
                    {
                        Patterns = new[] { "*.xls", "*.xlsx" }
                    }
                }
        });
        if (files != null && files.Count > 0)
        {
            var selectedFile = files[0];// 获取用户选择的文件
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            //打开文件流
            //异步地释放实现了 IAsyncDisposable 接口的资源
            //selectedFile.OpenReadAsync() 是一个异步方法，它会返回一个 Task<Stream>，表示一个异步操作，最终会返回一个文件流（Stream）。
            /*await using 是 C# 8.0 引入的语法糖，用于异步地释放实现了 IAsyncDisposable 接口的资源。

        Stream 实现了 IAsyncDisposable 接口，因此可以使用 await using 来确保文件流在使用完毕后能够异步地释放资源。

        传统的 using 语句只能同步释放资源（调用 Dispose 方法），而 await using 会调用 DisposeAsync 方法，确保资源能够异步释放。*/
            await using (var stream = await selectedFile.OpenReadAsync())
            {
                // 使用 EPPlus 操作 Excel 文件  
                using (var packageTemplate = new OfficeOpenXml.ExcelPackage(stream))
                {
                    var wb = packageTemplate.Workbook;// 获取工作簿

                    //接下去对每一个sht表进行操作
                    //按照之前的逻辑将台账生成模板中的所有养殖户生成一下台账
                    string assetsPath = Path.Combine(AppContext.BaseDirectory, "Assets");
                    string ledgerTemplatesPath = Path.Combine(assetsPath, "LedgerTemplates");
                    string templatePathSwine = Path.Combine(ledgerTemplatesPath, "04生猪大型养殖场（户）台账.xlsx");
                    string templatePathCow = Path.Combine(ledgerTemplatesPath, "10牛大型养殖场（户）台账.xlsx");
                    string templatePathSheepAndGoat = Path.Combine(ledgerTemplatesPath, "13羊大型养殖场（户）台账.xlsx");
                    string templatePathPoultry = Path.Combine(ledgerTemplatesPath, "07家禽大型养殖场（户）台账.xlsx");
                    ExcelWorksheet sht养殖户目录 = wb.Worksheets[0];
                    int rowCount = sht养殖户目录.Dimension.Rows;
                    int columnCount = sht养殖户目录.Dimension.Columns;
                    if (rowCount == 1)
                    {
                        await MyMessagebox.ShowAsyncInMainWindow("没有填写规模户信息", Icon.Error, ButtonEnum.Ok);
                        return;
                    }
                    //using (var packageSwineLedger = new ExcelPackage(templatePathSwine))
                    //using (var packageCowLedger = new ExcelPackage(templatePathCow))
                    //using (var packageSheepAndGoatLedger = new ExcelPackage(templatePathSheepAndGoat))
                    //using (var packagePoultryLedger = new ExcelPackage(templatePathPoultry))
                    using (var packageLegder = new ExcelPackage())
                    {
                        int 户主姓名列 = 3;
                        int 调查户编码列 = 4;
                        int 畜种类型列 = 5;
                        int 养殖户类型列 = 6;
                        var worksheetCoverPage = packageLegder.Workbook.Worksheets.Add("目录");
                        for (int i = 2; i <= rowCount; i++)
                        {

                            if ((sht养殖户目录.Cells[i, 养殖户类型列].Value == null ? "未填写规模户是大型还是小型" : sht养殖户目录.Cells[i, 养殖户类型列].Value.ToString()).Equals("D") == false)
                            {
                                continue;
                            }
                            if (sht养殖户目录.Cells[i, 畜种类型列].Value.ToString().Equals("生猪"))
                            {

                                //把生猪台账模板复制过来
                                using (var packageSwineLedger = new ExcelPackage(new FileInfo(templatePathSwine)))
                                {
                                    ExcelWorkbook wb生猪台账 = packageSwineLedger.Workbook;
                                    ExcelWorksheet? sht正文 = packageSwineLedger.Workbook.Worksheets[2];
                                    //ExcelWorksheet? sht正文 = null;
                                    //foreach (ExcelWorksheet item in packageSwineLedger.Workbook.Worksheets)
                                    //{
                                    //    if (item.Name.Contains("正文"))
                                    //    {
                                    //        sht正文 = item;
                                    //        break;
                                    //    }
                                    //}
                                    //if (sht正文 == null)
                                    //{
                                    //    await MyMessagebox.ShowAsyncInMainWindow("模板文件中没有找到正文表", Icon.Error, ButtonEnum.Ok);
                                    //    return;
                                    //}
                                    string singleFarmingName = $"{sht养殖户目录.Cells[i, 畜种类型列].Value.ToString()}+{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    var sht = packageLegder.Workbook.Worksheets.Add(singleFarmingName, sht正文);
                                    sht.Cells[2, 1].Value = $"调查户（单位）名称：{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    sht.Cells[3, 1].Value = $"调查户代码：{sht养殖户目录.Cells[i, 调查户编码列].Value.ToString()}";
                                }
                            }
                            else if (sht养殖户目录.Cells[i, 畜种类型列].Value.ToString().Equals("牛"))
                            {

                                //把牛台账模板复制过来
                                using (var packageCowLedger = new ExcelPackage(new FileInfo(templatePathCow)))
                                {

                                    ExcelWorksheet? sht正文 = packageCowLedger.Workbook.Worksheets[2];

                                    string singleFarmingName = $"{sht养殖户目录.Cells[i, 畜种类型列].Value.ToString()} + {sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    var sht = packageLegder.Workbook.Worksheets.Add(singleFarmingName, sht正文);
                                    sht.Cells[3, 1].Value = $"调查户（单位）名称：{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    sht.Cells[4, 1].Value = $"调查户代码：{sht养殖户目录.Cells[i, 调查户编码列].Value.ToString()}";
                                }
                            }
                            else if (sht养殖户目录.Cells[i, 畜种类型列].Value.ToString().Equals("羊"))
                            {
                                using (var packageCowLedger = new ExcelPackage(new FileInfo(templatePathSheepAndGoat)))
                                {

                                    ExcelWorksheet? sht正文 = packageCowLedger.Workbook.Worksheets[2];

                                    string singleFarmingName = $"{sht养殖户目录.Cells[i, 畜种类型列].Value.ToString()}+{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    var sht = packageLegder.Workbook.Worksheets.Add(singleFarmingName, sht正文);
                                    sht.Cells[2, 1].Value = $"调查户（单位）名称：{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    sht.Cells[3, 1].Value = $"调查户代码：{sht养殖户目录.Cells[i, 调查户编码列].Value.ToString()}";
                                }
                            }
                            else if (sht养殖户目录.Cells[i, 畜种类型列].Value.ToString().Contains("禽"))
                            {
                                using (var packageCowLedger = new ExcelPackage(new FileInfo(templatePathPoultry)))
                                {

                                    ExcelWorksheet? sht正文 = packageCowLedger.Workbook.Worksheets[2];

                                    string singleFarmingName = $"{sht养殖户目录.Cells[i, 畜种类型列].Value.ToString()}+{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    var sht = packageLegder.Workbook.Worksheets.Add(singleFarmingName, sht正文);
                                    sht.Cells[3, 1].Value = $"调查户（单位）名称：{sht养殖户目录.Cells[i, 户主姓名列].Value.ToString()}";
                                    sht.Cells[4, 1].Value = $"调查户代码：{sht养殖户目录.Cells[i, 调查户编码列].Value.ToString()}";
                                }
                            }
                        }

                        // 保存生成的台账
                        //var ledgerFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "GeneratedLedger.xlsx");
                        var ledgerFilePath = Path.Combine(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "审核程序文件夹"), "GeneratedLedger.xlsx");
                        packageLegder.SaveAs(new FileInfo(ledgerFilePath));
                    }
                }
            }
            await MyMessagebox.ShowAsyncInMainWindow("台账生成成功", Icon.Success, ButtonEnum.Ok);
        }

    }
    private void CopySheetContent(ExcelWorksheet sourceSheet, ExcelWorksheet targetSheet)
    {
        int rows = sourceSheet.Dimension?.Rows ?? 0;
        int columns = sourceSheet.Dimension?.Columns ?? 0;

        for (int row = 1; row <= rows; row++)
        {
            for (int col = 1; col <= columns; col++)
            {
                targetSheet.Cells[row, col].Value = sourceSheet.Cells[row, col].Value;
                sourceSheet.Cells[row, col].CopyStyles(targetSheet.Cells[row, col]);

            }
        }
    }

    private Window GetMainWindow()
    {
        if (Application.Current?.ApplicationLifetime is IClassicDesktopStyleApplicationLifetime desktop)
        {
            return desktop.MainWindow;
        }
        return null;
    }

    public async Task GetAllAnimalKind()
    {
        if (string.IsNullOrWhiteSpace(SelectedSingleExcelLedgerPath))
        {
            await MyMessagebox.ShowAsyncInMainWindow("请先选择台账文件", Icon.Error, ButtonEnum.Ok);
            return;
        }
        using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(fileName:SelectedSingleExcelLedgerPath)))
        {
            //try
            //{
            var wb = package.Workbook;// 获取工作簿
                                      //接下去对每一个sht表进行操作
            if (wb.Worksheets.Count == 0)
            {
                await MyMessagebox.ShowAsyncInMainWindow("选择的台账没有sheet表!", Icon.Error, ButtonEnum.Ok);
                return;
            }
            AnimalKindList.Clear();

            foreach (var sht in wb.Worksheets)
            {

                if (sht.Cells[1, 1].Value != null && sht.Cells[1, 1].Value.ToString().Contains("养殖") && sht.Cells[1, 1].Value.ToString().Contains("台账") && sht.Cells[1, 1].Value.ToString().Contains("猪"))
                {

                    if (!AnimalKindList.Contains("生猪"))
                    {
                        AnimalKindList.Add("生猪");
                        
                    }
                    sheetsOfSwine.Add(sht);
                }
               else if (sht.Cells[1, 1].Value != null && sht.Cells[1, 1].Value.ToString().Contains("养殖") && sht.Cells[1, 1].Value.ToString().Contains("台账") && sht.Cells[1, 1].Value.ToString().Contains("牛"))
                {

                    if (!AnimalKindList.Contains("牛"))
                    {
                        AnimalKindList.Add("牛");
                        
                    }
                    sheetsOfCow.Add(sht);
                }
               else if (sht.Cells[1, 1].Value != null && sht.Cells[1, 1].Value.ToString().Contains("养殖") && sht.Cells[1, 1].Value.ToString().Contains("台账") && sht.Cells[1, 1].Value.ToString().Contains("羊"))
                {

                    if (!AnimalKindList.Contains("羊"))
                    {
                        AnimalKindList.Add("羊");
                        
                    }
                    sheetsOfGoat.Add(sht);
                }
               else if (sht.Cells[1, 1].Value != null && sht.Cells[1, 1].Value.ToString().Contains("养殖") && sht.Cells[1, 1].Value.ToString().Contains("台账") && sht.Cells[1, 1].Value.ToString().Contains("家禽"))
                {

                    if (!AnimalKindList.Contains("家禽"))
                    {
                        AnimalKindList.Add("家禽");
                        
                    }
                    sheetsOfPoultry.Add(sht);
                }

            }

            if (AnimalKindList.Count >0)
            {
                SelectedAnimalKind = AnimalKindList[0];
            }

            //}
            //catch (InvalidOperationException ex)
            //{
            //    await MyMessagebox.ShowAsyncInMainWindow("台账正在被占用,请先关闭", Icon.Error, ButtonEnum.Ok);
            //}
        }
    }
    public async Task ShowTheDataInLedger()
    {
        if (string.IsNullOrWhiteSpace(SelectedSingleExcelLedgerPath))
        {
            await MyMessagebox.Show("请先选择台账文件",Icon.Warning);
            return;
        }
        if (SelectedAnimalKind == null)
        {
            await MyMessagebox.ShowAsyncInMainWindow("请先选择要展示的畜种", Icon.Error, ButtonEnum.Ok);
            return;
        }
        using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(fileName: SelectedSingleExcelLedgerPath)))
        {
            //try
            //{
            var wb = package.Workbook;// 获取工作簿
                                      //接下去对每一个sht表进行操作
            if (wb.Worksheets.Count == 0)
            {
                await MyMessagebox.ShowAsyncInMainWindow("选择的台账没有sheet表!", Icon.Error, ButtonEnum.Ok);
                return;
            }
            if (SelectedAnimalKind.Equals("生猪"))
            {
                int 开始行 = 9;
                int[] 季度列 = { 7, 11, 15, 19 };
                for (int i = 0; i < sheetsOfSwine.Count; i++)
                {

                    ExcelWorksheet shtSingle = wb.Worksheets[sheetsOfSwine[i].Name];
                    var 养殖户名称 = GetFarmName(shtSingle, 2, 1);
                    var 养殖户编码 = GetFarmCode(shtSingle, 3, 1);
                    for (int j = 0; j < 季度列.Length; j++)
                    {


                        var 时期 = ((季度列[j] -3)/4).ToString();
                        var 当季出栏数 = Convert.ToDouble(shtSingle.Cells[开始行 + 10, 季度列[j]].Value ?? "0") + Convert.ToDouble(shtSingle.Cells[开始行 + 11, 季度列[j]].Value ?? "0");
                        var 期末存栏合计 = Convert.ToDouble(shtSingle.Cells[开始行, 季度列[j]].Value == null ? "0" : shtSingle.Cells[开始行, 季度列[j]].Value.ToString());
                        var 二十五公斤以下仔猪存栏 = Convert.ToDouble(shtSingle.Cells[开始行 + 1, 季度列[j]].Value ?? "0");
                        var 待育肥猪存栏 = Convert.ToDouble(shtSingle.Cells[开始行 + 2, 季度列[j]].Value ?? "0");
                        var 五十公斤以上待育肥猪存栏 = Convert.ToDouble(shtSingle.Cells[开始行 + 3, 季度列[j]].Value ?? "0");
                        var 种猪存栏 = Convert.ToDouble(shtSingle.Cells[开始行 + 4, 季度列[j]].Value ?? "0");
                        var 能繁母猪存栏 = Convert.ToDouble(shtSingle.Cells[开始行 + 5, 季度列[j]].Value ?? "0");
                        var 当季增加头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 6, 季度列[j]].Value ?? "0");
                        var 自繁头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 7, 季度列[j]].Value ?? "0");
                        var 购进头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 8, 季度列[j]].Value ?? "0");
                        var 当季减少头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 9, 季度列[j]].Value ?? "0");
                        var 自宰头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 10, 季度列[j]].Value ?? "0");

                        var 出售肥猪头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 11, 季度列[j]].Value ?? "0");
                        var 出售肥猪金额 = Convert.ToDouble(shtSingle.Cells[开始行 + 12, 季度列[j]].Value ?? "0");
                        var 出售肥猪重量 = Convert.ToDouble(shtSingle.Cells[开始行 + 13, 季度列[j]].Value ?? "0");
                        var 其他原因减少 = Convert.ToDouble(shtSingle.Cells[开始行 + 14, 季度列[j]].Value ?? "0");
                        var 出售25公斤以下仔猪头数 = Convert.ToDouble(shtSingle.Cells[开始行 + 15, 季度列[j]].Value ?? "0");
                        var 出售25公斤以下仔猪金额 = Convert.ToDouble(shtSingle.Cells[开始行 + 16, 季度列[j]].Value ?? "0");
                        var 出售25公斤以下仔猪重量 = Convert.ToDouble(shtSingle.Cells[开始行 + 17, 季度列[j]].Value ?? "0");
                        PreReviewSwineMsgs massPig = new PreReviewSwineMsgs(时期,
                                养殖户名称, 当季出栏数, 期末存栏合计, 二十五公斤以下仔猪存栏, 待育肥猪存栏, 五十公斤以上待育肥猪存栏, 种猪存栏,
                               能繁母猪存栏, 当季增加头数, 自繁头数, 购进头数, 当季减少头数, 自宰头数, 出售肥猪头数, 出售肥猪金额, 出售肥猪重量,
                               其他原因减少, 出售25公斤以下仔猪头数, 出售25公斤以下仔猪金额, 出售25公斤以下仔猪重量
                           );
                        MassPigCln.Add(massPig);
                    }
                    var 合计时期 = "截止到本季度";
                    //var 合计当季出栏数 = MassPigCln.Where(x=>x.户主姓名.Equals(养殖户名称)).Sum(x =>x.当季出栏数00);
                    var 本养殖户数据 = MassPigCln.Where(x => x.户主姓名.Equals(养殖户名称)).AsEnumerable();
                    var 合计当季出栏数 = 本养殖户数据.Sum(x => x.当季出栏数00);
                    var 合计期末存栏合计 = 本养殖户数据.Where(x=>x.时期.Equals(_seasonNow.ToString())).Select(x=>x.期末存栏合计01).SingleOrDefault();//如果为空返回double的默认值,不抛出异常
                    var 合计二十五公斤以下仔猪存栏 = 本养殖户数据.Where(x => x.时期.Equals(_seasonNow.ToString())).Select(x => x.T25公斤以下仔猪存栏数02).SingleOrDefault();
                    var 合计待育肥猪存栏 = 本养殖户数据.Where(x => x.时期.Equals(_seasonNow.ToString())).Select(x => x.待育肥猪存栏数03).SingleOrDefault();
                    var 合计五十公斤以上待育肥猪存栏 = 本养殖户数据.Where(x => x.时期.Equals(_seasonNow.ToString())).Select(x => x.T50公斤以上待育肥猪存栏数04).SingleOrDefault();
                    var 合计种猪存栏 = 本养殖户数据.Where(x => x.时期.Equals(_seasonNow.ToString())).Select(x => x.种猪存栏数05).SingleOrDefault();
                    var 合计能繁母猪存栏 = 本养殖户数据.Where(x => x.时期.Equals(_seasonNow.ToString())).Select(x => x.能繁殖母猪存栏数06).SingleOrDefault();
                    var 合计当季增加头数 = 本养殖户数据.Sum(x => x.当季增加头数07);
                    var 合计自繁头数 = 本养殖户数据.Sum(x => x.自繁头数08);
                    var 合计购进头数 = 本养殖户数据.Sum(x => x.购进头数09);
                    var 合计当季减少头数 = 本养殖户数据.Sum(x => x.当季减少头数10); ;
                    var 合计自宰头数 = 本养殖户数据.Sum(x => x.自宰肥猪头数11);

                    var 合计出售肥猪头数 = 本养殖户数据.Sum(x => x.出售肥猪头数12);
                    var 合计出售肥猪金额 = 本养殖户数据.Sum(x => x.出售肥猪金额元13);
                    var 合计出售肥猪重量 = 本养殖户数据.Sum(x => x.出售肥猪重量kg14);
                    var 合计其他原因减少 = 本养殖户数据.Sum(x => x.其它原因减少15);
                    var 合计出售25公斤以下仔猪头数 = 本养殖户数据.Sum(x => x.出售25公斤以下仔猪头数16);
                    var 合计出售25公斤以下仔猪金额 = 本养殖户数据.Sum(x => x.出售25公斤以下仔猪金额元17);
                    var 合计出售25公斤以下仔猪重量 = 本养殖户数据.Sum(x => x.出售25公斤以下仔猪重量kg18);
                    PreReviewSwineMsgs sumMassPig = new PreReviewSwineMsgs(合计时期,
                               养殖户名称, 合计当季出栏数, 合计期末存栏合计, 合计二十五公斤以下仔猪存栏, 合计待育肥猪存栏, 合计五十公斤以上待育肥猪存栏, 合计种猪存栏,
                              合计能繁母猪存栏, 合计当季增加头数, 合计自繁头数, 合计购进头数, 合计当季减少头数, 合计自宰头数, 合计出售肥猪头数, 合计出售肥猪金额, 合计出售肥猪重量,
                              合计其他原因减少, 合计出售25公斤以下仔猪头数, 合计出售25公斤以下仔猪金额, 合计出售25公斤以下仔猪重量
                          );
                    MassPigCln.Add(sumMassPig);
                }
            }
        }
        
    }
    private string GetFarmCode(ExcelWorksheet sht, int 行, int 列)
    {
        char 冒号;
        string 单元格内容 = Convert.ToString(sht.Cells[行, 列].Value);
        if (Regex.IsMatch(单元格内容, "：") == true)
        {
            冒号 = '：';
        }
        else if (Regex.IsMatch(单元格内容, ":") == true)
        {
            冒号 = ':';
        }
        else
        {
            return ($"[{sht.Name}]户编码填写不符合规范,把冒号漏了");

        }
        int INT_冒号位置 = 单元格内容.IndexOf(冒号);
        string 单元格冒号后内容 = 单元格内容.Substring(INT_冒号位置 + 1).Trim();

        string numberPattern = @"\d+";

        MatchCollection matches = Regex.Matches(单元格冒号后内容, numberPattern);
        if (matches.Count == 0)
        {
            return $"[{sht.Name}]未填写户编码";

        }
        // 将所有匹配到的数字拼接为一个字符串
        string result = "";
        foreach (Match match in matches)
        {
            result += match.Value;
        }
        return result;
    }
    private string GetFarmName(ExcelWorksheet sht, int 行, int 列)
    {
        char 冒号;
        string 单元格内容 = Convert.ToString(sht.Cells[行, 列].Value);
        if (Regex.IsMatch(单元格内容, "：") == true)
        {
            冒号 = '：';
        }
        else if (Regex.IsMatch(单元格内容, ":") == true)
        {
            冒号 = ':';
        }
        else
        {
            return ($"[{sht.Name}]户名称填写不符合规范,把冒号漏了");

        }
        int INT_冒号位置 = 单元格内容.IndexOf(冒号);
        string 单元格冒号后内容 = 单元格内容.Substring(INT_冒号位置 + 1).Trim();

        if (单元格冒号后内容 == null || Regex.IsMatch(单元格内容, "[\u4e00-\u9fa5]") == false)
        {
            return "户主姓名填写错误";
        }
        return 单元格冒号后内容.Trim('_');
    }
}